import pandas as pd
from fastapi import APIRouter, Request
from fastapi.templating import Jinja2Templates
from fastapi.responses import JSONResponse

api_ajax = APIRouter()


# 加载并分析数据
df = pd.read_excel("./data/销售记录.xlsx")

@api_ajax.get("/getdata")
async def get_data():

    result = [120, 200, 150]

    return JSONResponse(result)

@api_ajax.get("/total_by_customer/")
async def total_by_customer():
    result = df.groupby('客户名称')['金额'].sum().reset_index().to_dict(orient='records')
    return JSONResponse(content=result)

@api_ajax.get("/orders_by_province/")
async def orders_by_province():
    result = df.groupby('客户省份')['订单编号'].count().reset_index().to_dict(orient='records')
    return JSONResponse(result)

@api_ajax.get("/average_delay/")
async def average_delay():
    df['下单日期'] = pd.to_datetime(df['下单日期'])
    df['实际送货日期'] = pd.to_datetime(df['实际送货日期'])
    df['送货延迟天数'] = (df['实际送货日期'] - df['下单日期']).dt.days
    avg_delay = df['送货延迟天数'].mean()
    return {"average_delay": avg_delay}